import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

# In order to analyse the dataset correctly, I add this setting to be able to view maximum rows available instead of a summarized version of it.

pd.set_option('display.max_rows', None)

listings = pd.read_csv("C:/Users/STARIZ.PK/Desktop/DA_COURSE/listingssummary.csv")

listings

listings.shape

(22106, 18)

listings.columns

Index(['id', 'name', 'host_id', 'host_name', 'neighbourhood', 'latitude',
       'longitude', 'room_type', 'price', 'minimum_nights',
       'number_of_reviews', 'last_review', 'reviews_per_month',
       'calculated_host_listings_count', 'availability_365',
       'number_of_reviews_ltm', 'license'],
      dtype='object')

listings.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 22106 entries, 0 to 22105
Data columns (total 18 columns):
 #   Column                          Non-Null Count  Dtype  
---  ------                          --------------  -----  
 0   id                              22106 non-null  int64  
 1   name                            22106 non-null  object 
 2   host_id                         22106 non-null  int64  
 3   host_name                       22106 non-null  object 
 4   neighbourhood_group             0 non-null      float64
 5   neighbourhood                   22106 non-null  object 
 6   latitude                        22106 non-null  float64
 7   longitude                       22106 non-null  float64
 8   room_type                       22106 non-null  object 
 9   price                           21591 non-null  float64
 10  minimum_nights                  22106 non-null  int64  
 11  number_of_reviews               22106 non-null  int64  
 12  last_review                     17725 non-null  object 
 13  reviews_per_month               17725 non-null  float64
 14  calculated_host_listings_count  22106 non-null  int64  
 15  availability_365                22106 non-null  int64  
 16  number_of_reviews_ltm           22106 non-null  int64  
 17  license                         7887 non-null   object 
dtypes: float64(5), int64(7), object(6)
memory usage: 3.0+ MB

listings.duplicated()

0        False
1        False
2        False
3        False
4        False
         ...  
22101    False
22102    False
22103    False
22104    False
22105    False
Length: 22106, dtype: bool

duplicate_count = listings.duplicated().sum()

print("Number of duplicate rows:", duplicate_count)

Number of duplicate rows: 0

listings.isna().sum()

id                                    0
name                                  0
host_id                               0
host_name                             0
neighbourhood_group               22106
neighbourhood                         0
latitude                              0
longitude                             0
room_type                             0
price                               515
minimum_nights                        0
number_of_reviews                     0
last_review                        4381
reviews_per_month                  4381
calculated_host_listings_count        0
availability_365                      0
number_of_reviews_ltm                 0
license                           14219
dtype: int64

try:
    listings = listings.drop(columns=['neighbourhood_group'])
    print("Dropped 'neighbourhood_group' column.")
except KeyError:
    print("The column 'neighbourhood_group' does not exist in the DataFrame.")

Dropped 'neighbourhood_group' column.

listings[listings['price'].isna()]
print("\nRows with NaN values in 'price':")
print(listings[listings['price'].isna()])

Rows with NaN values in 'price':
                        id                                             name  \
20                24083960                              L'atelier nel verde   
45      645517290310155601  GS1 - Cozy room with private balcony near MM1&2   
107    1083300041408285466                   Luminoso bilocale ultimo piano   
108               54069348     [S.Ambrogio/Duomo] •Jacuzzi • Luxury •design   
263               34266101                      Italianway - Pastrengo 11 B   
...                    ...                                              ...   
21620  1080977210968618989                      Junior Suite Montenapoleone   
21621   646254813413567289         Piccolo appartamento nel cuore di Milano   
21622   662696039783062089                        SUITE SANTA TECLA GLAMOUR   
21623   984567430017161063                     Casa nuova al centro di nolo   
21624  1080983782702319760               Suite Presidenziale Montenapoleone   

         host_id   host_name           neighbourhood   latitude  longitude  \
20      59688698       Paolo            VILLAPIZZONE  45.494220   9.139050   
45     276248169      Andrea  BUENOS AIRES - VENEZIA  45.482967   9.218021   
107     64597882       Paolo                  LORETO  45.496330   9.217629   
108      1829939         Luz                   DUOMO  45.459390   9.177420   
263     27693585  Italianway                   ISOLA  45.486520   9.186680   
...          ...         ...                     ...        ...        ...   
21620  559397155       Masai                   DUOMO  45.468532   9.193033   
21621   14918482   Fortunata                   GRECO  45.498970   9.212000   
21622  101839090  Maria Sole                   DUOMO  45.461600   9.193680   
21623   89603949     Mariano                  LORETO  45.494383   9.217973   
21624  559397155       Masai                   DUOMO  45.468620   9.193140   

             room_type  price  minimum_nights  number_of_reviews last_review  \
20        Private room    NaN               2                  1  2022-09-11   
45        Private room    NaN              30                  0         NaN   
107    Entire home/apt    NaN               3                  0         NaN   
108    Entire home/apt    NaN               1                109  2024-03-13   
263    Entire home/apt    NaN               1                  7  2022-07-28   
...                ...    ...             ...                ...         ...   
21620  Entire home/apt    NaN               1                  0         NaN   
21621  Entire home/apt    NaN               7                  0         NaN   
21622  Entire home/apt    NaN               1                  0         NaN   
21623  Entire home/apt    NaN               3                  0         NaN   
21624  Entire home/apt    NaN               1                  0         NaN   

       reviews_per_month  calculated_host_listings_count  availability_365  \
20                  0.05                               1                89   
45                   NaN                              55                52   
107                  NaN                               1                74   
108                 5.23                               2                 0   
263                 0.13                             489               244   
...                  ...                             ...               ...   
21620                NaN                               4                 0   
21621                NaN                               1                85   
21622                NaN                              10               180   
21623                NaN                               1               264   
21624                NaN                               4                 0   

       number_of_reviews_ltm           license  
20                         0               NaN  
45                         0               NaN  
107                        0               NaN  
108                       82  015146-CNI-04749  
263                        0  015146-CIM-00186  
...                      ...               ...  
21620                      0               NaN  
21621                      0               NaN  
21622                      0               NaN  
21623                      0               NaN  
21624                      0               NaN  

[515 rows x 17 columns]

listings.loc[20, 'price'] = 200; listings.loc[45, 'price'] = 80; listings.loc[107, 'price'] = 119; listings.loc[263, 'price'] = 122; listings.loc[21620, 'price'] = 402;
listings.loc[21621, 'price'] = 60; listings.loc[21622, 'price'] = 300; listings.loc[21624, 'price'] = 679; listings.loc[264, 'price'] = 350;
listings.loc[265, 'price'] = 297; listings.loc[270, 'price'] = 80; listings.loc[21616, 'price'] = 250; listings.loc[21617, 'price'] = 250;
listings.loc[21618, 'price'] = 142; listings.loc[21613, 'price'] = 250; listings.loc[21614, 'price'] = 80; listings.loc[21615, 'price'] = 300;
listings.loc[21612, 'price'] = 220; listings.loc[935, 'price'] = 185; listings.loc[1158, 'price'] = 80; listings.loc[1159, 'price'] = 80;
listings.loc[1160, 'price'] = 60; listings.loc[1480, 'price'] = 503; listings.loc[1688, 'price'] = 48; listings.loc[1690, 'price'] = 38;
listings.loc[1691, 'price'] = 130; listings.loc[1695, 'price'] = 73; listings.loc[1700, 'price'] = 380; listings.loc[1703, 'price'] = 900;
listings.loc[1705, 'price'] = 67; listings.loc[1723, 'price'] = 145; listings.loc[2113, 'price'] = 32; listings.loc[2118, 'price'] = 200;
listings.loc[2120, 'price'] = 260; listings.loc[2324, 'price'] = 97; listings.loc[2336, 'price'] = 55; listings.loc[2337, 'price'] = 175;
listings.loc[2340, 'price'] = 380; listings.loc[2617, 'price'] = 62; listings.loc[2706, 'price'] = 230; listings.loc[3326, 'price'] = 88;
listings.loc[3327, 'price'] = 55; listings.loc[3684, 'price'] = 99; listings.loc[3808, 'price'] = 126; listings.loc[3849, 'price'] = 500;
listings.loc[4386, 'price'] = 165; listings.loc[4795, 'price'] = 280; listings.loc[4864, 'price'] = 98; listings.loc[5147, 'price'] = 86;
listings.loc[5149, 'price'] = 76; listings.loc[5151, 'price'] = 100; listings.loc[5153, 'price'] = 150; listings.loc[5157, 'price'] = 118;
listings.loc[5361, 'price'] = 100; listings.loc[5363, 'price'] = 80; listings.loc[5368, 'price'] = 45; listings.loc[5369, 'price'] = 95;
listings.loc[5372, 'price'] = 211; listings.loc[5373, 'price'] = 400; listings.loc[5474, 'price'] = 124; listings.loc[5480, 'price'] = 250;
listings.loc[5635, 'price'] = 263; listings.loc[5637, 'price'] = 139; listings.loc[5721, 'price'] = 600; listings.loc[5725, 'price'] = 153;
listings.loc[5726, 'price'] = 153; listings.loc[5734, 'price'] = 91; listings.loc[6332, 'price'] = 50; listings.loc[6333, 'price'] = 150;
listings.loc[6520, 'price'] = 105; listings.loc[6522, 'price'] = 200; listings.loc[6683, 'price'] = 110; listings.loc[6688, 'price'] = 922;
listings.loc[7203, 'price'] = 250; listings.loc[7592, 'price'] = 98; listings.loc[7595, 'price'] = 290; listings.loc[7596, 'price'] = 144;
listings.loc[7602, 'price'] = 295; listings.loc[7997, 'price'] = 120; listings.loc[8192, 'price'] = 267; listings.loc[8519, 'price'] = 106;
listings.loc[8521, 'price'] = 90; listings.loc[8665, 'price'] = 102; listings.loc[8961, 'price'] = 103; listings.loc[9320, 'price'] = 899;
listings.loc[9343, 'price'] = 120; listings.loc[9344, 'price'] = 55; listings.loc[9350, 'price'] = 149; listings.loc[9565, 'price'] = 103;
listings.loc[9566, 'price'] = 180; listings.loc[9569, 'price'] = 350; listings.loc[9571, 'price'] = 120; listings.loc[9576, 'price'] = 300;
listings.loc[9580, 'price'] = 204; listings.loc[9584, 'price'] = 94; listings.loc[9588, 'price'] = 51; listings.loc[9589, 'price'] = 164;
listings.loc[9591, 'price'] = 90; listings.loc[9757, 'price'] = 116; listings.loc[9773, 'price'] = 236; listings.loc[9834, 'price'] = 112;
listings.loc[9864, 'price'] = 135; listings.loc[9968, 'price'] = 208; listings.loc[9969, 'price'] = 200; listings.loc[9970, 'price'] = 76;
listings.loc[9975, 'price'] = 90; listings.loc[10363, 'price'] = 250; listings.loc[10898, 'price'] = 43; listings.loc[10900, 'price'] = 94;
listings.loc[10916, 'price'] = 400; listings.loc[11499, 'price'] = 140; listings.loc[11962, 'price'] = 150; listings.loc[12001, 'price'] = 193;
listings.loc[12009, 'price'] = 450; listings.loc[12362, 'price'] = 138; listings.loc[12363, 'price'] = 120; listings.loc[12364, 'price'] = 99;
listings.loc[12669, 'price'] = 2000; listings.loc[12677, 'price'] = 100; listings.loc[12679, 'price'] = 90; listings.loc[12681, 'price'] = 74;
listings.loc[12682, 'price'] = 100; listings.loc[12683, 'price'] = 46; listings.loc[12686, 'price'] = 80; listings.loc[12688, 'price'] = 101;
listings.loc[12869, 'price'] = 89; listings.loc[12872, 'price'] = 60; listings.loc[12873, 'price'] = 600; listings.loc[12877, 'price'] = 93;
listings.loc[12882, 'price'] = 170; listings.loc[12883, 'price'] = 500; listings.loc[13079, 'price'] = 350; listings.loc[13082, 'price'] = 80;
listings.loc[13113, 'price'] = 120; listings.loc[13114, 'price'] = 211; listings.loc[13117, 'price'] = 85; listings.loc[13119, 'price'] = 50;
listings.loc[13122, 'price'] = 124; listings.loc[13124, 'price'] = 100; listings.loc[13332, 'price'] = 130; listings.loc[13333, 'price'] = 87;
listings.loc[13337, 'price'] = 103; listings.loc[13338, 'price'] = 99; listings.loc[13341, 'price'] = 250; listings.loc[13343, 'price'] = 45;
listings.loc[13604, 'price'] = 120; listings.loc[13713, 'price'] = 86; listings.loc[14128, 'price'] = 149; listings.loc[14274, 'price'] = 86; 
listings.loc[14299, 'price'] = 322; listings.loc[14303, 'price'] = 145; listings.loc[14491, 'price'] = 253; listings.loc[14494, 'price'] = 92;
listings.loc[14657, 'price'] = 100; listings.loc[14664, 'price'] = 109; listings.loc[15128, 'price'] = 69; listings.loc[15244, 'price'] = 120;
listings.loc[15544, 'price'] = 89; listings.loc[15545, 'price'] = 118; listings.loc[15546, 'price'] = 65; listings.loc[15547, 'price'] = 206;
listings.loc[15551, 'price'] = 105; listings.loc[15552, 'price'] = 200; listings.loc[15553, 'price'] = 165; listings.loc[15554, 'price'] = 60;
listings.loc[15555, 'price'] = 100; listings.loc[15558, 'price'] = 300; listings.loc[15559, 'price'] = 130; listings.loc[15560, 'price'] = 80;
listings.loc[15739, 'price'] = 210; listings.loc[15741, 'price'] = 80; listings.loc[15744, 'price'] = 100; listings.loc[15748, 'price'] = 199;
listings.loc[15749, 'price'] = 121; listings.loc[15754, 'price'] = 750; listings.loc[15756, 'price'] = 373; listings.loc[15904, 'price'] = 142; 
listings.loc[15956, 'price'] = 185; listings.loc[15969, 'price'] = 155; listings.loc[16194, 'price'] = 85; listings.loc[16209, 'price'] = 120;
listings.loc[16210, 'price'] = 140; listings.loc[16638, 'price'] = 118; listings.loc[16861, 'price'] = 35; listings.loc[16879, 'price'] = 101;
listings.loc[17181, 'price'] = 200; listings.loc[17187, 'price'] = 125; listings.loc[17188, 'price'] = 143; listings.loc[17191, 'price'] = 250;
listings.loc[17775, 'price'] = 108; listings.loc[18109, 'price'] = 245; listings.loc[18110, 'price'] = 200; listings.loc[18113, 'price'] = 292;
listings.loc[18118, 'price'] = 62; listings.loc[18119, 'price'] = 179; listings.loc[18318, 'price'] = 35; listings.loc[18324, 'price'] = 200;
listings.loc[18325, 'price'] = 60; listings.loc[18328, 'price'] = 475; listings.loc[18329, 'price'] = 124; listings.loc[18330, 'price'] = 108;
listings.loc[18333, 'price'] = 74; listings.loc[18622, 'price'] = 200; listings.loc[18728, 'price'] = 85; listings.loc[18730, 'price'] = 85;
listings.loc[18731, 'price'] = 132; listings.loc[19304, 'price'] = 130; listings.loc[19345, 'price'] = 284; listings.loc[19634, 'price'] = 102;
listings.loc[19636, 'price'] = 69; listings.loc[19637, 'price'] = 90; listings.loc[20446, 'price'] = 180; listings.loc[20475, 'price'] = 70;
listings.loc[20476, 'price'] = 136; listings.loc[20478, 'price'] = 305; listings.loc[20482, 'price'] = 500; listings.loc[20486, 'price'] = 472;
listings.loc[20674, 'price'] = 150; listings.loc[20675, 'price'] = 139; listings.loc[20677, 'price'] = 120; listings.loc[20682, 'price'] = 119;
listings.loc[20685, 'price'] = 180; listings.loc[20686, 'price'] = 98; listings.loc[20687, 'price'] = 100; listings.loc[20688, 'price'] = 120;
listings.loc[21282, 'price'] = 100; listings.loc[21493, 'price'] = 60; listings.loc[21497, 'price'] = 70; listings.loc[21498, 'price'] = 900;
listings.loc[21500, 'price'] = 300; listings.loc[21509, 'price'] = 90; listings.loc[21517, 'price'] = 100; listings.loc[21527, 'price'] = 75;
listings.loc[21531, 'price'] = 200; listings.loc[21532, 'price'] = 160; listings.loc[21537, 'price'] = 75; listings.loc[21540, 'price'] = 180;
listings.loc[21544, 'price'] = 140; listings.loc[21550, 'price'] = 450; listings.loc[21551, 'price'] = 80; listings.loc[21552, 'price'] = 400;
listings.loc[21556, 'price'] = 100; listings.loc[21557, 'price'] = 85; listings.loc[21558, 'price'] = 90; listings.loc[21559, 'price'] = 60;
listings.loc[21560, 'price'] = 145; listings.loc[21561, 'price'] = 35; listings.loc[21562, 'price'] = 333; listings.loc[21564, 'price'] = 160;
listings.loc[21567, 'price'] = 160; listings.loc[21568, 'price'] = 300; listings.loc[21569, 'price'] = 335; listings.loc[21570, 'price'] = 194;
listings.loc[21571, 'price'] = 300; listings.loc[21574, 'price'] = 180; listings.loc[21576, 'price'] = 84; listings.loc[21578, 'price'] = 138;
listings.loc[21581, 'price'] = 250; listings.loc[21584, 'price'] = 1000; listings.loc[21586, 'price'] = 70; listings.loc[21589, 'price'] = 100;
listings.loc[21590, 'price'] = 140; listings.loc[21597, 'price'] = 500; listings.loc[21600, 'price'] = 200; listings.loc[21602, 'price'] = 143;
listings.loc[21606, 'price'] = 399; listings.loc[21607, 'price'] = 290; listings.loc[21609, 'price'] = 200

# Dropping the two rows as both are not visible on the website and had more than 2 columns with NaN values

try:
    listings = listings.drop([1074, 1477, 1697, 1825, 2123, 2333, 2338, 2456, 2709, 3678, 4865, 5364, 5365, 5366])
    print("Dropped listings unavailable on the airbnb website")
except KeyError:
    print("The rows requested do not exist in the DataFrame.")

The rows requested do not exist in the DataFrame.

# Dropping the two rows as both are not visible on the website and had more than 2 columns with NaN values

try:
    listings = listings.drop([5375, 5724, 6521, 6681, 7482, 7589, 7597, 7600, 7618, 9045, 9348, 9574, 9581, 9863, 10542, 11330, 11846])
    print("Dropped listings unavailable on the airbnb website")
except KeyError:
    print("The rows requested do not exist in the DataFrame.")

Dropped listings unavailable on the airbnb website

# Dropping the two rows as both are not visible on the website and had more than 2 columns with NaN values

try:
    listings = listings.drop([12670, 12875, 13109, 13328, 13338, 13340, 14300, 14660, 15245, 15557, 15561, 15752, 18326, 18733, 19465, 19639, 20148, 20484, 20487, 20692, 21554, 21563, 21572, 21601, 21605])
    print("Dropped listings unavailable on the airbnb website")
except KeyError:
    print("The rows requested do not exist in the DataFrame.")

Dropped listings unavailable on the airbnb website

try:
    listings = listings.drop([2125])
    print("Dropped listings unavailable on the airbnb website")
except KeyError:
    print("The rows requested do not exist in the DataFrame.")

Dropped listings unavailable on the airbnb website

try:
    listings = listings.drop([21623, 4358, 9585, 21555, 21573, 21594, 21599])
    print("Dropped listings unavailable to all the guests")
except KeyError:
    print("The rows requested do not exist in the DataFrame.")

Dropped listings unavailable to all the guests

# Manually fetched monthly prices
monthly_prices_by_index = {
    1161: 21350, 1822: 3534, 2319: 593, 4348: 10332, 
    5152: 1800, 5154: 3799, 9047: 2400, 9340: 1875, 11845: 2147, 
    12366: 2070, 12881: 2250, 15243: 1488, 
    16072: 2100, 18116: 3600, 21491: 2135, 
    21592: 2929, 21603: 2320
}

for index, monthly_price in monthly_prices_by_index.items():
    nightly_price = monthly_price / 30
    print(f"Processing row index: {index}, Monthly Price: {monthly_price}, Nightly Price: {nightly_price}")
    listings.loc[index, 'price'] = nightly_price

Processing row index: 1161, Monthly Price: 21350, Nightly Price: 711.6666666666666
Processing row index: 1822, Monthly Price: 3534, Nightly Price: 117.8
Processing row index: 2319, Monthly Price: 593, Nightly Price: 19.766666666666666
Processing row index: 4348, Monthly Price: 10332, Nightly Price: 344.4
Processing row index: 5152, Monthly Price: 1800, Nightly Price: 60.0
Processing row index: 5154, Monthly Price: 3799, Nightly Price: 126.63333333333334
Processing row index: 9047, Monthly Price: 2400, Nightly Price: 80.0
Processing row index: 9340, Monthly Price: 1875, Nightly Price: 62.5
Processing row index: 11845, Monthly Price: 2147, Nightly Price: 71.56666666666666
Processing row index: 12366, Monthly Price: 2070, Nightly Price: 69.0
Processing row index: 12881, Monthly Price: 2250, Nightly Price: 75.0
Processing row index: 15243, Monthly Price: 1488, Nightly Price: 49.6
Processing row index: 16072, Monthly Price: 2100, Nightly Price: 70.0
Processing row index: 18116, Monthly Price: 3600, Nightly Price: 120.0
Processing row index: 21491, Monthly Price: 2135, Nightly Price: 71.16666666666667
Processing row index: 21592, Monthly Price: 2929, Nightly Price: 97.63333333333334
Processing row index: 21603, Monthly Price: 2320, Nightly Price: 77.33333333333333

# Convert 'last_review' column to datetime type
listings['last_review'] = pd.to_datetime(listings['last_review'])

# Compute mode of 'last_review' column
mode_last_review = listings['last_review'].mode()[0]

# Impute missing values with mode
listings['last_review'].fillna(mode_last_review, inplace=True)

median_reviews_per_month = listings['reviews_per_month'].median()
listings['reviews_per_month'].fillna(median_reviews_per_month, inplace=True)

mode_license = listings['license'].mode()[0]  # Calculate mode license
listings['license'].fillna(mode_license, inplace=True)  # Fill NaN values with mode

listings.isna().sum() # checking for total null values

id                                  0
name                                0
host_id                             0
host_name                           0
neighbourhood                       0
latitude                            0
longitude                           0
room_type                           0
price                             191
minimum_nights                      0
number_of_reviews                   0
last_review                         0
reviews_per_month                   0
calculated_host_listings_count      0
availability_365                    0
number_of_reviews_ltm               0
license                             0
dtype: int64

available_for_2_years = listings[(listings['availability_365'] >= 730) | (listings['price'].isna())]
not_available_for_2_years = listings[(listings['availability_365'] < 730) & (~listings['price'].isna())]

plt.figure(figsize=(10, 6))
sns.countplot(data=available_for_2_years, x='neighbourhood', color='blue')
plt.title('Listings Available for the Next 2 Years by Neighbourhood')
plt.xlabel('Neighbourhood')
plt.ylabel('Number of Listings')
plt.xticks(rotation=90)
plt.tight_layout()
plt.show()

plt.figure(figsize=(12, 6))
sns.histplot(data=listings, x='price', hue='room_type', multiple='stack', palette='Set1', bins=50)
plt.title('Price Distribution by Room Type')
plt.xlabel('Price')
plt.ylabel('Count')
plt.xlim(0, 1000)

C:\ProgramData\anaconda3\Lib\site-packages\seaborn\_oldcore.py:1119: FutureWarning: use_inf_as_na option is deprecated and will be removed in a future version. Convert inf values to NaN before operating instead.
  with pd.option_context('mode.use_inf_as_na', True):

(0.0, 1000.0)

avg_price_neighbourhood = listings.groupby('neighbourhood')['price'].mean().reset_index()
avg_price_neighbourhood = avg_price_neighbourhood.sort_values(by='price', ascending=False).head(20)

# Visualizing with a bar plot
plt.figure(figsize=(14, 10))
sns.barplot(x='price', y='neighbourhood', data=avg_price_neighbourhood, color='blue')
plt.title('Average Price by Neighbourhood', fontsize=16)
plt.xlabel('Average Price', fontsize=14)
plt.ylabel('Neighbourhood', fontsize=14)
plt.xticks(fontsize=12)
plt.yticks(fontsize=12)
plt.show()

# Select columns for correlation analysis
columns_of_interest = ['price', 'number_of_reviews', 'minimum_nights', 'availability_365']

sns.pairplot(listings[columns_of_interest])
plt.show()

C:\ProgramData\anaconda3\Lib\site-packages\seaborn\_oldcore.py:1119: FutureWarning: use_inf_as_na option is deprecated and will be removed in a future version. Convert inf values to NaN before operating instead.
  with pd.option_context('mode.use_inf_as_na', True):
C:\ProgramData\anaconda3\Lib\site-packages\seaborn\_oldcore.py:1119: FutureWarning: use_inf_as_na option is deprecated and will be removed in a future version. Convert inf values to NaN before operating instead.
  with pd.option_context('mode.use_inf_as_na', True):
C:\ProgramData\anaconda3\Lib\site-packages\seaborn\_oldcore.py:1119: FutureWarning: use_inf_as_na option is deprecated and will be removed in a future version. Convert inf values to NaN before operating instead.
  with pd.option_context('mode.use_inf_as_na', True):
C:\ProgramData\anaconda3\Lib\site-packages\seaborn\_oldcore.py:1119: FutureWarning: use_inf_as_na option is deprecated and will be removed in a future version. Convert inf values to NaN before operating instead.
  with pd.option_context('mode.use_inf_as_na', True):

# Collect data by host_id and host_name to count listings per host
top_hosts = listings.groupby(['host_id', 'host_name']).size().reset_index(name='num_listings')

# Sort the hosts based on the number of listings
top_hosts = top_hosts.sort_values(by='num_listings', ascending=False)

# Display the top hosts
print("Top Hosts based on Number of Listings:")
print(top_hosts.head(10))  # Display top 10 hosts

Top Hosts based on Number of Listings:
        host_id       host_name  num_listings
3990   27693585      Italianway           488
1764    9025189  Easylife House           272
2763   16366171  Cleanbnb House           258
954     4417813   The Best Rent           155
541     2504885          Simone           128
8514  230140176        Youhosty           124
5639   54050937          I-Host           116
3022   18593362         Hintown           115
8300  207076474        Federica           108
2597   14743308   Casa Da Suite           104

listings.describe()

	id	host_id	latitude	longitude	price	minimum_nights	number_of_reviews	last_review	reviews_per_month	calculated_host_listings_count	availability_365	number_of_reviews_ltm
count	2.206700e+04	2.206700e+04	22067.000000	22067.000000	21876.000000	22067.000000	22067.000000	22067	22067.000000	22067.000000	22067.000000	22067.000000
mean	5.481877e+17	1.723837e+08	45.471680	9.187790	172.000643	3.546880	33.951557	2023-09-22 09:54:52.318846976	1.468002	30.531654	182.359451	12.149998
min	6.400000e+03	1.054800e+04	45.396920	9.062310	14.000000	1.000000	0.000000	2012-09-10 00:00:00	0.010000	1.000000	0.000000	0.000000
25%	3.756590e+07	1.822470e+07	45.454981	9.169561	79.000000	1.000000	1.000000	2023-10-14 00:00:00	0.410000	1.000000	83.000000	0.000000
50%	7.258167e+17	6.996357e+07	45.471187	9.187460	106.000000	2.000000	8.000000	2024-02-25 00:00:00	0.910000	2.000000	175.000000	3.000000
75%	9.644061e+17	3.293559e+08	45.486980	9.209924	160.000000	2.000000	31.000000	2024-03-17 00:00:00	1.760000	11.000000	270.000000	14.000000
max	1.119382e+18	5.685713e+08	45.535420	9.271249	90000.000000	500.000000	1206.000000	2024-03-25 00:00:00	28.370000	489.000000	365.000000	483.000000
std	4.536214e+17	1.894870e+08	0.020867	0.029632	796.160919	13.412662	76.027247	NaN	1.737872	84.177401	113.005374	21.030294

	id	name	host_id	host_name	neighbourhood_group	neighbourhood	latitude	longitude	room_type	price	minimum_nights	number_of_reviews	last_review	reviews_per_month	calculated_host_listings_count	availability_365	number_of_reviews_ltm	license
0	39185194	The Best Rent - One bedroom apartment in Milan	4417813	The Best Rent	NaN	RONCHETTO SUL NAVIGLIO	45.441440	9.140470	Entire home/apt	175.0	1	4	2022-04-27	0.12	155	0	0	015146-LNI-00331
1	17818971	Studio nel cuore delle cinque vie	20016667	Carlotta	NaN	DUOMO	45.461430	9.184950	Private room	200.0	1	1	2023-04-20	0.09	2	357	1	NaN
2	1077526403195603262	PrimoPiano - Arrivabene	2504885	Simone	NaN	BOVISA	45.500170	9.165999	Entire home/apt	108.0	2	1	2024-02-12	0.70	128	68	1	015146-LIM-01075
3	582962332344164114	Italianway - Venini 16	27693585	Italianway	NaN	BUENOS AIRES - VENEZIA	45.487240	9.212029	Entire home/apt	112.0	1	26	2023-11-07	1.07	489	257	4	015146-CIM-04360
4	626361430623671090	One Suite Meravigli - 5 mins from DUOMO	458997695	Davide	NaN	DUOMO	45.465786	9.181277	Entire home/apt	134.0	2	100	2024-02-18	4.56	1	180	47	CIR: 015146-LNI-00888
5	609195525998335685	Casa Loca	162807431	Lodovico	NaN	SELINUNTE	45.468790	9.144350	Entire home/apt	120.0	4	8	2023-09-24	0.70	2	276	8	NaN
6	871543278373949043	Nel cuore di Brera	422809692	Giovanni	NaN	BRERA	45.476670	9.188860	Private room	127.0	1	0	NaN	NaN	1	360	0	NaN
7	17101691	Bilo Apples	101593898	Antonio	NaN	CENTRALE	45.489830	9.203430	Entire home/apt	107.0	3	67	2024-03-16	0.78	1	326	12	NaN
8	5160010	home feeling/casa dolce casa	6382445	Andrea	NaN	CENTRALE	45.493110	9.207290	Private room	70.0	3	14	2024-03-03	0.64	1	172	5	NaN
9	12340607	Wonderful private flat/studio in Porta Romana ...	4465773	Cinzia	NaN	PORTA ROMANA	45.450040	9.200600	Entire home/apt	180.0	2	1	2018-04-19	0.01	1	364	0	NaN
10	18813646	station central 3rooms free parking	26724588	Daniel	NaN	CENTRALE	45.488730	9.205350	Entire home/apt	111.0	1	377	2023-11-21	4.55	6	356	31	NaN
11	33791612	Camera&bagno privato est. - Private room&services	17321621	Ettore	NaN	WASHINGTON	45.459810	9.156250	Private room	62.0	1	20	2023-10-01	0.33	2	83	2	NaN
12	1039269	Breethin' Milan Loft	3920646	Marco	NaN	TIBALDI	45.437450	9.184670	Entire home/apt	50.0	2	21	2018-01-03	0.16	1	0	0	NaN
13	49900549	"Central Milan Oasis" V2	195861371	Valeria	NaN	DUOMO	45.465290	9.198700	Entire home/apt	62.0	15	22	2023-11-20	0.67	19	31	6	NaN
14	1115622402949588162	(DUOMO 15 min) Zona Isola - Fast Wi-Fi	532751355	Cozy Short Stay	NaN	ISOLA	45.493990	9.190920	Entire home/apt	102.0	2	0	NaN	NaN	2	262	0	CIR: 015146-CNI-07353
15	968762028485765606	Luxury apartment just renewed	222740789	Edoardo	NaN	VIGENTINA	45.452579	9.190373	Entire home/apt	247.0	1	4	2023-10-22	0.62	1	0	4	NaN
16	41988715	Wonderful apartment in Navigli (015146-CNI-02762)	332485599	Roberto	NaN	RONCHETTO SUL NAVIGLIO	45.443270	9.147100	Entire home/apt	106.0	2	30	2023-06-29	0.60	1	10	9	NaN
17	723674416622780230	New Naviglio Nest\nCozy Apartment with Patio	152799556	Eugenia	NaN	S. CRISTOFORO	45.444870	9.152370	Entire home/apt	175.0	2	6	2023-06-11	0.34	1	161	3	NaN
18	870777043851556418	Stanza singola privata	251519239	Annalisa	NaN	MECENATE	45.446703	9.243198	Private room	60.0	1	4	2024-02-27	0.61	1	365	4	NaN
19	861387293883278139	La casa di Patti	99590973	Micol	NaN	BANDE NERE	45.456238	9.144575	Entire home/apt	90.0	2	12	2023-12-10	1.06	1	74	12	015146-LNI-02452
20	24083960	L'atelier nel verde	59688698	Paolo	NaN	VILLAPIZZONE	45.494220	9.139050	Private room	NaN	2	1	2022-09-11	0.05	1	89	0	NaN
21	8950366	Coworking BM24	4111302	Daniela	NaN	BUENOS AIRES - VENEZIA	45.481060	9.208410	Shared room	150.0	2	0	NaN	NaN	2	365	0	NaN
22	912749311243438972	Cozy apartment close to Bocconi	436264104	Andrea	NaN	VIGENTINA	45.448385	9.196159	Entire home/apt	101.0	1	0	NaN	NaN	1	94	0	NaN
23	820292059202265104	Comfortable orange big room in Porta Romana	30920497	Lucia	NaN	PORTA ROMANA	45.449620	9.201530	Private room	68.0	1	64	2024-03-24	4.84	3	332	61	Licenza 015146-CNI-05571
24	1109236818217912284	Modern loft in Porta Romana	1554399	Alessandra	NaN	UMBRIA - MOLISE	45.454850	9.218900	Entire home/apt	350.0	5	0	NaN	NaN	3	269	0	NaN
25	618454783896157403	Design district suite room - Milano	16912336	Stefania	NaN	MAGENTA - S. VITTORE	45.458019	9.168271	Private room	200.0	2	10	2023-04-22	0.45	1	72	1	NaN
26	805808986495420962	Cozy Studio Brenta	428209007	Barbara	NaN	LODI - CORVETTO	45.442110	9.216900	Entire home/apt	71.0	1	28	2024-01-27	2.07	2	211	27	015146-CIM-05732
27	1114442732039726075	Appartamento • centro storico	226166015	Filippo	NaN	PORTA ROMANA	45.454360	9.203910	Entire home/apt	128.0	3	0	NaN	NaN	1	11	0	NaN
28	1076611197872539517	Centro Storico - Camera Doppia	345768925	Alberto	NaN	NAVIGLI	45.456570	9.169580	Private room	70.0	2	0	NaN	NaN	3	257	0	NaN
29	1100492993177648476	Lodi Suite	545337494	Dolcerent	NaN	LODI - CORVETTO	45.435314	9.234650	Entire home/apt	60.0	1	0	NaN	NaN	9	229	0	NaN
30	1002274319868460669	Appartamento di prestigio vicino alla metro	332268332	Caroline	NaN	BANDE NERE	45.457310	9.137480	Entire home/apt	86.0	1	12	2024-03-03	2.95	8	300	12	NaN
31	2137508	duomo suite	10908423	Leda	NaN	DUOMO	45.465720	9.192270	Entire home/apt	192.0	1	17	2023-09-30	0.14	1	140	6	NaN
32	681416622813676615	Luxury Villa in Milan	5758634	Verena	NaN	S. SIRO	45.475960	9.130490	Entire home/apt	1200.0	2	2	2022-10-10	0.11	1	142	0	NaN
33	16817846	Super BILOCALE! Vicino alla METRO comodo per D...	41558190	Michela	NaN	LORETO	45.489090	9.231000	Entire home/apt	80.0	4	119	2024-01-02	1.40	2	132	15	015146-LNI-02142
34	11115228	AS design apartment	20711857	Zoran E Dragana	NaN	BRERA	45.479760	9.184450	Entire home/apt	85.0	2	333	2024-03-09	3.38	1	226	30	015146-CNI-03980
35	10914331	Central and bright, in Porta Romana	56572541	Paola	NaN	XXII MARZO	45.455360	9.210260	Entire home/apt	169.0	4	89	2024-02-24	0.91	1	288	12	NaN
36	26406307	Excellent Rooms in Savona street	3008902	Jvonne Simona	NaN	GIAMBELLINO	45.448700	9.149820	Private room	45.0	10	5	2023-12-20	0.08	2	34	3	NaN
37	17453683	Stylish Loft with Balcony By The Center and Metro	6268116	Camilla	NaN	SARPI	45.478510	9.179670	Entire home/apt	157.0	2	21	2017-10-21	0.25	1	91	0	015146-LNI-05175
38	1006480201665970460	stanza Milano vicino centro	132489924	Ste	NaN	CANTALUPA	45.419836	9.157818	Private room	50.0	1	19	2024-03-22	3.83	1	73	19	NaN
39	1105858478905970600	Moderno bilocale in zona MiCo	565541492	Felice	NaN	VILLAPIZZONE	45.497285	9.148062	Entire home/apt	63.0	2	0	NaN	NaN	1	80	0	015146-LNI-05374

Exploratory Data Analysis of Airbnb Listings in Milan, Italy¶

Introduction¶

Milan is Italy's fashion and design capital. It is not only known as the centre of art, culture, and commerce but is also a vibrant destination for various travelers. With its rich history, stunning architecture, and bustling city life, Milan attracts millions of visitors each year.¶

Importing necessary libraries, the dataset file and reading the data before analysing any aspects of it¶

Shape of the dataframe:¶

We have total 22106 rows and 18 columns.¶

The below mentioned are all the columns names available in this dataframe:¶

The function performed below provides us with a concise summary of our dataframe's structure, including the data types of each column, the number of non-null values, and memory usage.¶

Now let's check if we have any duplicated rows in this dataframe.¶

In this dataframe, we have 0 duplicate rows.¶

Checking for total and dealing with null values:¶

All rows in the column, "neighbourhood_group" have NaN or missing values therefore we will drop this column completely:¶

Adding correct price value to those rows that had NaN values of price:¶

Some listings with more than 2 NaN values were not found on the website hence were unavailable to be booked or viewed. We drop them from our dataframe in order to work only with listings available to be booked or viewed on the Airbnb website.¶

Some listings had prices available at per month basis instead of per night. These listings are divided by 30 as an average number of days in a month to achieve the per night price. The NaN values are replaced with this per night price.¶

Dealing with NaN values in the columns, "last_review" and "reviews_per_month":¶

Imputing missing values in the last_review column with the mode date¶

Median Imputation for "reviews_per_month" column:¶

The median "reviews_per_month" provides a typical or representative value for listings, making it suitable for imputing missing values in cases where the exact value may not be known but a typical value is desired to get rid of missing values.¶

Replace NaN values in 'license' column with the mode (most frequent value)¶

Checking again for total null values:¶

We see that we are now left with only 191 NaN values in the column, "price". These have been thouroughly checked manually on the Airbnb website. They have NaN values because they are unavailable to book currently or till May 2026.¶

Availability Analysis:¶

Which listings are available or are not available for the next two years?¶

For Airbnb, having a good number of listings available in such a sought-after area over the next two years can be advantageous, potentially leading to higher booking rates and revenue generation.¶

Price Analysis:¶

Price distribution for different room types:¶

Airbnb can tailor its marketing campaigns based on room type preferences. For example, advertising campaigns can highlight the privacy and space of entire homes/apartments for families or groups, while emphasizing affordability and local experiences for private room listings.¶

Price by neighbourhood:¶

Calculating average prices by neighbourhood:¶

Calculate average prices by neighbourhood. Sorting them and limiting to the top 20.¶

Hosts in this area have the opportunity to generate higher revenue per booking compared to other neighborhoods. Airbnb can leverage this insight to tailor its marketing efforts and promotional strategies to attract both hosts and guests seeking upscale accommodations.¶

Price Correlation with other factors:¶

Host Analysis:¶

Top Hosts in Milan Airbnbs':¶

The analysis reveals the top hosts on the Airbnb platform, ranked by the number of listings they offer. Here are the key insights on the top 6 hosts:¶

1. Italianway (Host ID: 27693585):¶

Italianway emerges as the top host with an impressive 488 listings. Their substantial presence in the Airbnb marketplace signifies a strong foothold in the hospitality industry, offering a diverse range of accommodations to cater to various traveler preferences.¶

2. Easylife House (Host ID: 9025189):¶

Easylife House secures the second position with 272 listings, reflecting a significant commitment to providing lodging options for Airbnb guests.¶

3. Cleanbnb House (Host ID: 16366171):¶

Cleanbnb House follows closely with 258 listings, showcasing a dedication to maintaining clean and well-maintained accommodations for guests.¶

4. The Best Rent (Host ID: 4417813):¶

The Best Rent ranks fourth with 155 listings, demonstrating a notable presence in the Airbnb ecosystem.¶

5. Simone (Host ID: 2504885):¶

Simone features prominently with 128 listings, reflecting a strong entrepreneurial spirit and commitment to providing quality lodging experiences. Their offerings likely appeal to guests seeking personalized and authentic stays.¶

6. Youhosty (Host ID: 230140176):¶

Youhosty maintains a significant presence with 124 listings, highlighting a dedication to hosting and hospitality.¶

Summary statistics of the data:¶